using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Integrator.DAL
{
    public class SqlHelper
    {

        public string connString = "Server=localhost;Database=Integrator;User ID=sa;Password=sa;Connection Reset=FALSE;Min Pool Size=2;Connection Lifetime=120;Connection Timeout=60;";

        public long InsertParentRow(string sql, List<string> paramsName, List<object> paramsValue)
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                for (int i = 0; i < paramsName.Count; i++)
                {
                    cmd.Parameters.AddWithValue(paramsName[i], paramsValue[i]);
                }
                object result = cmd.ExecuteScalar();
                return result == null ? 0 : long.Parse(result.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void UpdateLog(AccessLog accessLog)
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[AccessLog]([UpdateTime] ,[MessageType], [MessageName], [MessageContent], [ClientAddress], [ClientType])VALUES(@UpdateTime ,@MessageType, @MessageName, @MessageContent, @ClientAddress, @ClientType)", conn);
                cmd.Parameters.AddWithValue("@ClientAddress", accessLog.ClientAddress);
                cmd.Parameters.AddWithValue("@ClientType", accessLog.ClientType);
                cmd.Parameters.AddWithValue("@MessageContent", accessLog.MessageContent);
                cmd.Parameters.AddWithValue("@MessageName", accessLog.MessageName);
                cmd.Parameters.AddWithValue("@MessageType", accessLog.MessageType);
                cmd.Parameters.AddWithValue("@UpdateTime", accessLog.UpdateTime);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void BulkInsertData(DataTable table)
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlBulkCopy copy = new SqlBulkCopy(conn);
                foreach (DataColumn col in table.Columns)
                {
                    copy.ColumnMappings.Add(col.ColumnName, col.ColumnName.ToLower());
                }
                copy.DestinationTableName = table.TableName;
                copy.WriteToServer(table);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
